﻿using HLStock.Common;
using HLStock.MyExcep;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;

namespace HLStock.DBAccess
{
    public class DbQuery<T> where T : class, new()
    {
        List<PropValueSetter> PropSetters;
        DbInstance instance;
        string tableName;
        List<OrderByField> orderFields = new List<OrderByField>();
        List<WhereConditional> whereFields = new List<WhereConditional>();
        public DbQuery(DbInstance instance)
        {
            this.instance = instance;
            PropSetters = DataAccessUtils.GetSetters<T>();
            tableName = DataAccessUtils.GetTableName<T>();
        }

        public DbQuery<T> Where(CondCollection whereConditionals)
        {
            whereFields.AddRange(whereConditionals.ToConds());
            return this;
        }
        public DbQuery<T> OrderBy(params OrderByField[] orderBy)
        {
            orderFields.AddRange(orderBy);
            return this;
        }

        private string ToSql(string select_part,bool add_order=true)
        {
            string where_path = " WHERE DEL = 'N' ";
            foreach (var item in whereFields)
            {
                where_path += item.ToSql();
            }
            string order_path = " ORDER BY ";
            if (orderFields.Count == 0) order_path += " 1 DESC,";
            foreach (var item in orderFields)
            {
                order_path += $"{item.Field},";
            }
            order_path = order_path.Substring(0, order_path.Length - 1);
            var sql = select_part + where_path + (add_order ? order_path : "");
            return sql;
        }
        private List<T> ReadAsList(IDataReader reader)
        {
            List<T> result = new List<T>();
            while (reader.Read())
            {
                T record = new T();
                foreach (var setter in PropSetters)
                {
                    setter.Set(reader, record);
                }
                result.Add(record);
            }
            reader.Close();
            return result;
        }

        private IDataReader MakeReader(string sql, bool clearWhereFields = true)
        {
            Dictionary<string, object?> p = new Dictionary<string, object?>();
            foreach (var field in whereFields)
            {
                if (!string.IsNullOrWhiteSpace(field.ParamName))
                {
                    p.Add(field.ParamName, field.Value);
                }
            }
            var cmd = instance.MakeQueryCommand(sql, p.ToArray());
            var dt1 = DateTime.Now;
            var r = cmd.ExecuteReader();
            var cost = (DateTime.Now - dt1).TotalMilliseconds;
            MyLogger.Sql("EXEC SQL={0} COST={1}ms", cmd.CommandText, cost);
            if (clearWhereFields)
            {
                whereFields.Clear();
                orderFields.Clear();
            }
            return r;
        }
        public List<T> ToList()
        {
            string slt_part = string.Format("SELECT * FROM \"{0}\"  ", tableName);
            var sql = ToSql(slt_part);
            var reader = MakeReader(sql);
            return ReadAsList(reader);
        }
        public int Count()
        {
            string slt_part = string.Format("SELECT COUNT(*) FROM \"{0}\"  ", tableName);
            string sql = ToSql(slt_part);
            using (var reader = MakeReader(sql))
            {
                if (reader.Read())
                {
                    var result = reader.GetInt32(0);
                    return result;
                }
            }
            throw new MyDbConnectException("无法执行语句" + sql);
        }
        public List<T> ToPageList(int pageSize, int pageIndex, out int totalCnt)
        {
            totalCnt = 0;
            string count_part = string.Format("SELECT COUNT(*) FROM \"{0}\"  ", tableName);
            string cont_sql = ToSql(count_part,false);
            using (var count_reader = MakeReader(cont_sql, false))
            {
                if (count_reader.Read())
                    totalCnt = count_reader.GetInt32(0);
            }
            string slt_part = string.Format("SELECT ROW_NUMBER() OVER(ORDER BY PK) RN__,T1.* FROM \"{0}\" T1  ", tableName);
            string sql = ToSql(slt_part, false);
            string page_sql = $"SELECT * FROM ({sql})T WHERE RN__ > {pageSize * (pageIndex - 1)} AND RN__ <= {pageSize * pageIndex}";
            using (var reader = MakeReader(page_sql))
            {
                var res = ReadAsList(reader);
                return res;
            }
        }
        public T? First()
        {
            string slt_part = string.Format("SELECT TOP 1 * FROM \"{0}\"  ", tableName);
            var sql = ToSql(slt_part);
            using (var reader = MakeReader(sql))
            {
                var result = ReadAsList(reader).FirstOrDefault();
                return result;
            }
        }
        public T? Get(string pk)
        {
            string sql = string.Format("SELECT * FROM \"{0}\"  WHERE DEL = 'N' AND PK = @PK", tableName);
            var cmd = instance.MakeQueryCommand(sql, new KeyValuePair<string, object?>("@PK", pk));
            using (var reader = cmd.ExecuteReader())
            {
                return ReadAsList(reader).FirstOrDefault();
            }
        }
    }
}
